# -*- coding:utf-8 -*-
import random
import pymysql

# 打开数据库连接
db = pymysql.connect(host="127.0.0.1", user="root", passwd="123456", db="cs", charset="utf8")
# 使用cursor()方法获取操作游标
cursor = db.cursor()

list_Xing = ['赵', '钱', '孙', '李', '周', '吴', '郑', '王', '冯', '陈', '褚', '卫', '蒋', '沈', '韩', '杨', '张', '李']
list_Ming = ['豫', '章', '故', '郡', '洪', '都', '新', '府', '星', '分', '翼', '轸', '地', '接', '衡', '饯', '子']
list_Hometown = ['北京', '河北', '河南', '天津', '海南', '兰州', '内蒙古', '甘肃', '西藏', '新疆', '东北']
# 初始化测试数据
# sql1='delete from student;'不能使用delete from 表名。如果这样的话Id会在上次的基础上增加。
sql1 = 'truncate table student;'
try:
    cursor.execute(sql1)
    db.commit()
except:
    db.rollback()

n = 0
while n < 20:
    n = n + 1
    name = random.choice(list_Xing) + random.choice(list_Ming) + random.choice(list_Ming)
    sage = random.randint(15, 25)
    ssex = random.randint(1, 2)
    hometown = random.choice(list_Hometown)
    high = random.randint(155, 190)
    class_id = random.randint(1, 6)
    # print name
    sql = '''insert into student values(null,'%s',%s,%s,'%s',%s,%s);''' % (name, sage, ssex, hometown, high, class_id)
    print
    sql
    # sql1 = "insert into student values(null,'{0}',18,1,'北京',175,2);".format(name)
    try:
        cursor.execute(sql)
    except:
        db.rollback()

try:
    # 提交到数据库执行
    db.commit()
except:
    # Rollback in case there is any error 发生错误时回滚
    db.rollback()
